package com.example.the_breakfast_club;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Helps create and manage queries for a database, which contains both User and
 * Item objects for the Find My Stuff application.
 * @author Team 19
 * @version 1.0
 */
public class DatabaseHandler extends SQLiteOpenHelper{
    
	// All Static variables
	// Singleton Variables
	private static DatabaseHandler instance = null;
	
    // Database Version
    private static final int DATABASE_VERSION = 1;
 
    // Database Name
    private static final String DATABASE_NAME = "stuffManager.db";
 
    // Table name
    private static final String TABLE_USERS = "users";
    private static final String TABLE_ITEMS = "items";
    
    // Users Table Columns names
    private static final String KEY_UNAME = "name";
    private static final String KEY_PASSWORD = "password";
    private static final String KEY_LOCKED = "locked"; // boolean
    private static final String KEY_CONTACT = "contact_info";
    private static final String KEY_ADMIN = "admin"; // boolean
    
    // Items Table Columns names
    private static final String KEY_INAME = "name";
    private static final String KEY_DESCRIPTION = "description";
    private static final String KEY_LOCATION = "location";
    private static final String KEY_CATEGORY = "category"; // int
    private static final String KEY_FOUND = "found"; // boolean
    private static final String KEY_RESOLVED = "resolved"; // boolean
    private static final String KEY_TIME = "time"; // int
    
    SimpleDateFormat sdf;
 
    /**
     * Constructs an instance of DatabaseHandler.
     * @param context Global information for the database.
     */
    @SuppressLint("SimpleDateFormat")
	public DatabaseHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        sdf = new SimpleDateFormat("yyyyMMdd");
    }
    
    /**
     * @see android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite.SQLiteDatabase)
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_USERS_TABLE = "CREATE TABLE " + TABLE_USERS + "("
                + KEY_UNAME + " TEXT," + KEY_PASSWORD + " TEXT,"
                + KEY_LOCKED + " INTEGER," + KEY_CONTACT + " TEXT,"
                + KEY_ADMIN + " INTEGER" + ")";
        db.execSQL(CREATE_USERS_TABLE);
        String CREATE_ITEMS_TABLE = "CREATE TABLE " + TABLE_ITEMS+ "("
                + KEY_INAME + " TEXT," + KEY_DESCRIPTION + " TEXT,"
                + KEY_LOCATION + " TEXT," + KEY_CATEGORY + " INTEGER," 
                + KEY_FOUND + " INTEGER," + KEY_RESOLVED + " INTEGER,"
                + KEY_TIME + " INTEGER" + ")";
        db.execSQL(CREATE_ITEMS_TABLE);
    }
    
    /**
     * @see android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite.SQLiteDatabase, int, int)
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_ITEMS);
 
        // Create tables again
        onCreate(db);
    }
    
    /**
     * Creates a DatabaseHandler if it does not yet exist; if it does, return
     * current instance.
     * @param context Global information for the database.
     * @return The database handler.
     */
    public static DatabaseHandler getInstance(Context context) {
    	if (instance == null) {
    		synchronized (DatabaseHandler.class) {
    			if (instance == null) {
    				instance = new DatabaseHandler(context);
    			}
    		}
    	}
    	return instance;
    }
    
    // All CRUD(Create, Read, Update, Delete) Operations
    
    /**
     * Adds a new user to the database.
     * @param user A user to be added.
     */
    public void addUser(User user) {
        SQLiteDatabase db = this.getWritableDatabase();
        if(getUser(user.getName()) == null) {
	        ContentValues values = new ContentValues();
	        values.put(KEY_UNAME, user.getName()); // User Name
	        values.put(KEY_PASSWORD, user.getPassword()); // User Password
	        values.put(KEY_LOCKED, user.getLockStatus()?1:0); // User Locked Status
	        values.put(KEY_CONTACT, user.getContactInfo()); // User Contact Info
	        values.put(KEY_ADMIN, user.isAdmin()?1:0); // User Admin Privileges
	        
	        // Inserting Row
	        db.insert(TABLE_USERS, null, values);
        }
        db.close(); // Closing database connection
    }
    
    /**
     * Queries the database given a username and returns a user object.
     * @param name The username of the user object to be retrieved.
     * @return A user object. Null if user does not exist.
     */
    public User getUser(String name) {
        SQLiteDatabase db = this.getReadableDatabase();
        
        Cursor cursor = db.query(TABLE_USERS, new String[] { KEY_UNAME,
                KEY_PASSWORD, KEY_LOCKED, KEY_CONTACT, KEY_ADMIN }, 
                KEY_UNAME + "=?",
                new String[] { name }, null, null, null, null);
        User user = null;
        if (cursor.moveToFirst()) {
        	user = new User(cursor.getString(0), cursor.getString(1),
        		cursor.getInt(2)==1, cursor.getString(3), 
        		cursor.getInt(4)==1);
        }
        // return user
        return user;
    }
    
    /**
     * Retrieves a list of all users in the database.
     * @return A list of all users.
     */
    public List<User> getAllUsers() {
        List<User> userList = new ArrayList<User>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_USERS;
     
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
     
        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                User user = new User();
                user.setName(cursor.getString(0));
                user.setPassword(cursor.getString(1));
                user.setLockStatus(cursor.getInt(2)==1);
                user.setContactInfo(cursor.getString(3));
                user.setAdminStatus(cursor.getInt(4)==1);
                // Adding user to list
                userList.add(user);
            } while (cursor.moveToNext());
        }
     
        // return user list
        return userList;
    }
     
    /**
     * Counts all the users in the database.
     * @return The number of users.
     */
    public int getUsersCount() {
        String countQuery = "SELECT  * FROM " + TABLE_USERS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        cursor.close();
 
        // return count
        return cursor.getCount();
    }
    
    /**
     * Makes changes to one user's information
     * @param user A user to be updated.
     * @return The number of rows affected: 0 if fail, 1 if success.
     */
    public int updateUser(User user) {
        SQLiteDatabase db = this.getWritableDatabase();
        
        ContentValues values = new ContentValues();
        values.put(KEY_PASSWORD, user.getPassword());
        values.put(KEY_LOCKED, user.getLockStatus()?1:0);
        values.put(KEY_CONTACT, user.getContactInfo());
        values.put(KEY_ADMIN, user.isAdmin()?1:0);
     
        // updating row
        return db.update(TABLE_USERS, values, KEY_UNAME + " = ?",
        		new String[] { user.getName() });
    }
    
    /**
     * Locks a user's account.
     * @param name The user to be locked.
     */
    public void lockUser(String name) {
    	User user = getUser(name);
    	if (user != null) {
    		user.setLockStatus(true);
    		updateUser(user);
    	}
    }
    
    /**
     * Unlocks a user's account.
     * @param name The user to be unlocked.
     */
    public void unlockUser(String name) {
    	User user = getUser(name);
    	if (user != null) {
    		user.setLockStatus(false);
    		updateUser(user);
    	}
    }
    
    /**
     * Deletes a user from the database.
     * @param user A user to be deleted.
     */
    public void deleteUser(User user) {
        SQLiteDatabase db = this.getWritableDatabase();
        if (getUser(user.getName()) != null) {
        	db.delete(TABLE_USERS, KEY_UNAME + " = ?",
                new String[] { user.getName() });
        }
        db.close();
    }
    
    /**
     * Checks to see if an account already exists.
     * @param name The username of the user object to be checked for.
     * @return True if exists; false otherwise.
     */
    public boolean accountExist(String name) {
        SQLiteDatabase db = this.getReadableDatabase();
        
        Cursor cursor = db.query(TABLE_USERS, new String[] { KEY_UNAME,
                KEY_PASSWORD, KEY_LOCKED, KEY_CONTACT, KEY_ADMIN }, 
                KEY_UNAME + "=?",
                new String[] { name }, null, null, null, null);
        if (cursor.getCount() < 1)
            return false;
        return true;
    }
    
    /**
     * Queries the database given a username and returns a locked status.
     * @param name The username of the user object to be retrieved.
     * @return True if the account is locked; false otherwise.
     */
    public boolean userIsLocked(String name) {
        SQLiteDatabase db = this.getReadableDatabase();
        
        Cursor cursor = db.query(TABLE_USERS, new String[] { KEY_UNAME,
                KEY_PASSWORD, KEY_LOCKED, KEY_CONTACT, KEY_ADMIN }, 
                KEY_UNAME + "=?",
                new String[] { name }, null, null, null, null);
        if (cursor != null) {
            cursor.moveToFirst();
            return cursor.getInt(2)==1?true:false;
        }
        return false;
    }
    
    /**
     * Queries the database given a username and returns a user object.
     * @param name The username of the user object to be retrieved.
     * @return True if the password matches the username; false otherwise.
     */
    public boolean verifyPassword(String username, String password) {
        SQLiteDatabase db = this.getReadableDatabase();
        
        Cursor cursor = db.query(TABLE_USERS, new String[] { KEY_UNAME,
                KEY_PASSWORD, KEY_LOCKED, KEY_CONTACT, KEY_ADMIN }, 
                KEY_UNAME + "=?",
                new String[] { username }, null, null, null, null);
        if (cursor != null) {
            cursor.moveToFirst();
            if (cursor.getString(1).equals(password))
            	return true;
        }
        return false;
    }

    /**
     * Changes a user's admin boolean to true.
     * @param name The username of the user to be changed.
     */
    public void setAdmin(String name) {
    	User user = getUser(name);
    	user.setAdminStatus(true);
    	updateUser(user);
    }
    
    /**
     * Queries the database given a username and returns whether the user is
     * an admin.
     * @param name The username of the user object to be retrieved.
     * @return True if the user is an admin; false otherwise.
     */
    public boolean getAdmin(String name) {
        SQLiteDatabase db = this.getReadableDatabase();
        
        Cursor cursor = db.query(TABLE_USERS, new String[] { KEY_UNAME,
                KEY_PASSWORD, KEY_LOCKED, KEY_CONTACT, KEY_ADMIN }, 
                KEY_UNAME + "=?",
                new String[] { name }, null, null, null, null);
        if (cursor != null) {
            cursor.moveToFirst();
            if (cursor.getInt(4)==1)
            	return true;
        }
        return false;
    }
    
    /**
     * Adds a new item to the database.
     * @param item An item to be added.
     */
    public void addItem(Item item) {
        SQLiteDatabase db = this.getWritableDatabase();
        
        ContentValues values = new ContentValues();
        values.put(KEY_INAME, item.getName()); // Item Name
        values.put(KEY_DESCRIPTION, item.getDescription()); // Item Description
        values.put(KEY_LOCATION, item.getLocation()); // Item Location
        values.put(KEY_CATEGORY, item.getCategory()); // Item Category
        values.put(KEY_FOUND, item.getFound()?1:0); // Lost or Found?
        values.put(KEY_RESOLVED, item.getResolved()?1:0); // Item Resolved Status
        values.put(KEY_TIME, Integer.parseInt(sdf.format(new Date()))); // Date
        
        // Inserting Row
        db.insert(TABLE_ITEMS, null, values);
        db.close(); // Closing database connection    	
    }
    
    /**
     * Queries the database given an item name and returns an item object.
     * @param name The item name of the item object to be retrieved.
     * @return An item object.
     */
    public Item getItem(String name) {
        SQLiteDatabase db = this.getReadableDatabase();
        
        Cursor cursor = db.query(TABLE_ITEMS, new String[] { KEY_INAME,
                KEY_DESCRIPTION, KEY_LOCATION, KEY_CATEGORY, KEY_FOUND, 
                KEY_RESOLVED, KEY_TIME }, KEY_INAME + "=?",
                new String[] { name }, null, null, null, null);
        if (cursor != null)
            cursor.moveToFirst();
     
        Item item = new Item(cursor.getString(0), cursor.getString(1),
        		cursor.getString(2), cursor.getInt(3), cursor.getInt(4)==1,
        		cursor.getInt(5)==1, cursor.getInt(6));
        // return item
        return item;
    }
    
    /**
     * Retrieves a list of all items in the database.
     * @return A list of all items.
     */
    public List<Item> getAllItems() {
        List<Item> itemList = new ArrayList<Item>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_ITEMS;
     
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
     
        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Item item = new Item();
                item.setName(cursor.getString(0));
                item.setDescription(cursor.getString(1));
                item.setLocation(cursor.getString(2));
                item.setCategory(cursor.getInt(3));
                item.setFound(cursor.getInt(4)==1);
                item.setResolved(cursor.getInt(5)==1);
                item.setTime(cursor.getInt(6));
                // Adding item to list
                itemList.add(item);
            } while (cursor.moveToNext());
        }
     
        // return item list
        return itemList;
    }
    
    /**
     * Retrieves a list of all items in the database, sorted by date.
     * @return A list of all items.
     */
    public List<Item> getAllItemsDateSort() {
        List<Item> itemList = new ArrayList<Item>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_ITEMS + " ORDER BY "
        		+ KEY_TIME;
     
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
     
        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Item item = new Item();
                item.setName(cursor.getString(0));
                item.setDescription(cursor.getString(1));
                item.setLocation(cursor.getString(2));
                item.setCategory(cursor.getInt(3));
                item.setFound(cursor.getInt(4)==1);
                item.setResolved(cursor.getInt(5)==1);
                item.setTime(cursor.getInt(6));
                // Adding item to list
                itemList.add(item);
            } while (cursor.moveToNext());
        }
     
        // return item list
        return itemList;
    }
    
    /**
     * Counts all the items in the database.
     * @return The number of items.
     */
    public int getItemsCount() {
        String countQuery = "SELECT  * FROM " + TABLE_ITEMS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        cursor.close();
 
        // return count
        return cursor.getCount();
    }
    
    /**
     * Makes changes to one item's information
     * @param item A item to be updated.
     * @return The number of rows affected: 0 if fail, 1 if success.
     */
    public int updateItem(Item item) {
        SQLiteDatabase db = this.getWritableDatabase();
        
        ContentValues values = new ContentValues();
        values.put(KEY_DESCRIPTION, item.getDescription());
        values.put(KEY_CATEGORY, item.getCategory());
        values.put(KEY_RESOLVED, item.getResolved());
     
        // updating row
        return db.update(TABLE_ITEMS, values, KEY_INAME + " = ?",
        		new String[] { item.getName() });
    }
    
    /**
     * Deletes an item from the database.
     * @param item An item to be deleted.
     */
    public void deleteItem(Item item) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_ITEMS, KEY_INAME + " = ?",
                new String[] { item.getName() });
        db.close();
    }
    
    /**
	 * Compares all items in lost list with all items in found list.
	 * @return true if a match was found, false otherwise
	 */
	public void checkForMatch(){
		int numLostItems = 0;
		int numFoundItems = 0; 

		List<Item> allItems = getAllItems();
		List<Item> foundItemList = new ArrayList<Item>();
		List<Item> lostItemList = new ArrayList<Item>();
		for(Item each : allItems) {
			if(each.getFound()){
				foundItemList.add(each);
				numFoundItems++;
			}
			else{
				lostItemList.add(each);
				numLostItems++;
			}
		}
		
		for(int i = 0; i < numLostItems; i++){
			Item currLost = lostItemList.get(i); //the lost item to compare
			for(int j = 0; j < numFoundItems; j++){
				Item currFound = foundItemList.get(j); //the found item to compare
				//compare all their values here.
				if((currLost.getName().equals(currFound.getName())) && 
						(currLost.getLocation().equals(currFound.getLocation()))){
					currLost.setResolved(true);
					updateItem(currLost);
					currFound.setResolved(true);
					updateItem(currFound);
				}
				
			}
		}
	}
}



